package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class TCforInfo
 */
//@WebServlet(description = "檢視測試項目的簽核資訊", urlPatterns = { "/TCforInfo" })
public class TCforInfo extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public TCforInfo() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String strSQL = "SELECT MAX(ver) FROM tms_mrtcmapping WHERE tpid=? AND mrid=?";
		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String returnResult = "";
		String mrid = request.getParameter("mrid");
		int ver = 0;
		ActionLog[] actionLog = null;
		CommentLog[] commentLog = null;
		FileUploadLog[] fileUploadLog = null;
		boolean isTCsubmitted = false;
		boolean isTCaccepted = false;
		boolean isTRsubmitted = false;
		boolean isTRaccepted = false;
		boolean isTCreviewed;
		boolean isTRreviewed;

		InfoMR infoMR = new InfoMR();

		ArrayList<TestCase> tcs = new ArrayList<TestCase>();

		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs = ps.executeQuery();
			rs.next();
			ver = rs.getInt(1);
			/** 取得現在最新的「版次」 **/
			strSQL = "SELECT t3.tcID,t2.uID, t2.tcName FROM tms_mrtcmapping t1,tms_testcase t2,tms_tcidmapping t3 WHERE t3.active=1 AND t3.uID=t1.tcuid AND t1.tcuid = t2.uID AND t1.tpid=? AND t1.mrid=? AND t1.ver=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			while (rs.next()) {
				TestCase tc = new TestCase();
				tc.setTcID(rs.getString("tcID"));
				tc.setuID(rs.getInt("uID"));
				tc.setTcName(rs.getString("tcName"));
				tcs.add(tc);
			}
			infoMR.setAssignedTC(tcs);
			
			
			/** 查詢歷史記錄 **/
			strSQL = "SELECT COUNT(*) FROM tms_actlog WHERE tpid=? AND mrid=? AND ver=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			rs.next();
			int count = rs.getInt(1);
			if (count > 0) {
				actionLog = new ActionLog[count];

				strSQL = "SELECT t3.nickName,t2.actName,t1.creator, t1.createdDate FROM tms_actlog t1, tms_acttype t2,tms_personnel t3 WHERE t1.creator=t3.userID AND t1.actSN=t2.actSN AND t1.tpid=? AND t1.mrid=? AND t1.ver=? ORDER BY t1.createdDate DESC";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, ver);
				rs = ps.executeQuery();

				count = 0;
				while (rs.next()) {
					actionLog[count] = new ActionLog();
					actionLog[count].setActionType(rs.getString("actName"));
					actionLog[count].setStartTime(rs.getTimestamp("createdDate").toString());
					actionLog[count].setUserID(rs.getString("creator"));
					actionLog[count].setUserName(rs.getString("nickName"));
					count++;
				}
				infoMR.setActionLog(actionLog);
			}

			/** 查詢註解的記錄 **/
			strSQL = "SELECT COUNT(*) FROM tms_mrcomment WHERE tpid=? AND mrid=? AND ver=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
			System.out.println("tpid:"+tpid);
			System.out.println("mrid:"+mrid);
			System.out.println("ver:"+ver);
			if (count > 0) {
				commentLog = new CommentLog[count];
System.out.println("COUNT:"+count);
				strSQL = "SELECT t2.nickName,t1.creator,t1.comment,t1.createdTime FROM tms_mrcomment t1,tms_personnel t2 WHERE t1.creator=t2.userID AND t1.tpid=? AND t1.mrid=? AND t1.ver=? ORDER BY t1.createdTime DESC";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, ver);
				rs = ps.executeQuery();

				count = 0;
				while (rs.next()) {
					System.out.println("WhileLoop Index:"+count);					
					commentLog[count] = new CommentLog();
					commentLog[count].setComment(rs.getString("comment"));
					commentLog[count].setStartTime(rs.getTimestamp("createdTime").toString());
					commentLog[count].setUserID(rs.getString("creator"));
					commentLog[count].setUserName(rs.getString("nickName"));
					count++;
				}
				infoMR.setCommentLog(commentLog);
			}
			
			/** 查詢上傳記錄 **/
			strSQL = "SELECT COUNT(*) FROM tms_mrfileupload WHERE tpid=? AND mrid=? AND ver=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
			if (count > 0) {
				fileUploadLog = new FileUploadLog[count];

				strSQL = "SELECT t1.creator,t1.sn,t1.ori_filename,t2.nickName,t1.createdTime FROM tms_mrfileupload t1, tms_personnel t2 WHERE t1.creator=t2.userID AND t1.tpid = ? AND t1.mrid = ? AND t1.ver = ? ORDER BY t1.createdTime DESC;";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, ver);
				rs = ps.executeQuery();

				count = 0;
				while (rs.next()) {
					fileUploadLog[count] = new FileUploadLog();
					fileUploadLog[count].setCreator(rs.getString("creator"));
					fileUploadLog[count].setUserName(rs.getString("nickName"));
					fileUploadLog[count].setCreatedTime(rs.getTimestamp("createdTime").toString());
					fileUploadLog[count].setFileName(rs.getString("ori_filename"));
					fileUploadLog[count].setSn(rs.getInt("sn"));
					count++;
				}
				infoMR.setFileUploadLog(fileUploadLog);
			}				
				
			
			/** 判斷目前「測試項目」的狀態 **/
			strSQL = "SELECT isTCreviewed,isTRreviewed,isTCsubmitted, isTCaccepted, isTRsubmitted, isTRaccepted  FROM tms_tpitem WHERE tpid=? AND mrid=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs = ps.executeQuery();
			rs.next();

			isTCsubmitted = rs.getInt("isTCsubmitted") == 1 ? true : false;
			isTCaccepted = rs.getInt("isTCaccepted") == 1 ? true : false;
			isTRsubmitted = rs.getInt("isTRsubmitted") == 1 ? true : false;
			isTRaccepted = rs.getInt("isTRaccepted") == 1 ? true : false;
			isTCreviewed = rs.getInt("isTCreviewed") == 1 ? true : false;
			isTRreviewed = rs.getInt("isTRreviewed") == 1 ? true : false;

			infoMR.setTCaccepted(isTCaccepted);
			infoMR.setTCsubmitted(isTCsubmitted);
			infoMR.setTRaccepted(isTRaccepted);
			infoMR.setTRsubmitted(isTRsubmitted);

			/** 表示已提交「測試案例」 **/
			if (isTCsubmitted) {
				strSQL = "SELECT t1.creator,t2.nickName, t1.createdDate FROM tms_actlog t1,tms_personnel t2 WHERE t1.creator=t2.userID AND t1.tpid=? AND t1.mrid=? AND t1.ver=? AND t1.actSN=1 ORDER BY t1.createdDate DESC";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, ver);
				rs = ps.executeQuery();

				if (rs.next()) {
					infoMR.setSubmitTC(rs.getString("nickName")+ "("+ rs.getString("creator") +")<br/>"
							+ rs.getTimestamp("createdDate").toString().subSequence(0, rs.getTimestamp("createdDate").toString().length()-2));
				}

				/** 表示已有審核測試「案例」 **/
				if (isTCreviewed) {
					strSQL = "SELECT t3.nickName,t2.isTCaccepted,t1.creator, t1.createdDate FROM tms_actlog t1,tms_tpitem t2,tms_personnel t3 WHERE t1.creator=t3.userID AND t1.tpid=t2.tpid AND t1.mrid=t2.mrid AND t1.tpid=? AND t1.mrid=? AND t1.ver=? AND t1.actSN=3 ORDER BY t1.createdDate DESC";

					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tpid);
					ps.setString(2, mrid);
					ps.setInt(3, ver);
					rs = ps.executeQuery();

					if (rs.next()) {
						infoMR.setReviewTC((rs.getInt("isTCaccepted") == 1 ? "通過："
								: "拒絕：")
								+ rs.getString("nickName")+ "("+ rs.getString("creator")+")<br/>"
								+ rs.getTimestamp("createdDate").toString().subSequence(0, rs.getTimestamp("createdDate").toString().length()-2));
					}
				}

			}

			/** 表示已提交「測試結果」 **/
			if (isTRsubmitted) {
				strSQL = "SELECT t2.nickName, t1.creator, t1.createdDate FROM tms_actlog t1,tms_personnel t2 WHERE t1.creator=t2.userID AND t1.tpid=? AND t1.mrid=? AND t1.ver=? AND t1.actSN=2 ORDER BY t1.createdDate DESC";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, ver);
				rs = ps.executeQuery();
				if (rs.next()) {
					infoMR.setSubmitTR(rs.getString("nickName")+ "("+ rs.getString("creator") +")<br/>" + rs.getTimestamp("createdDate").toString().subSequence(0, rs.getTimestamp("createdDate").toString().length()-2));
				}

				/** 表示已有審核測試結果 **/
				if (isTRreviewed) {
					strSQL = "SELECT t3.nickName,t2.isTRaccepted,t1.creator, t1.createdDate FROM tms_actlog t1,tms_tpitem t2,tms_personnel t3 WHERE t1.creator=t3.userID AND t1.tpid=t2.tpid AND t1.mrid=t2.mrid AND t1.tpid=? AND t1.mrid=? AND t1.ver=? AND t1.actSN=4 ORDER BY t1.createdDate DESC";

					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tpid);
					ps.setString(2, mrid);
					ps.setInt(3, ver);
					rs = ps.executeQuery();

					if (rs.next()) {
						infoMR.setReviewTR((rs.getInt("isTRaccepted") == 1 ? "通過："
								: "拒絕：")
								+ rs.getString("nickName")+ "("+ rs.getString("creator")+")<br/>"
								+ rs.getTimestamp("createdDate").toString().subSequence(0, rs.getTimestamp("createdDate").toString().length()-2));
					}
				}

			}
			returnResult = new Gson().toJson(infoMR);

		} catch (SQLException e) {
			returnResult = "{\"errorMsg\":\"" + wid.keepErrorMes(conn, ps, e, "", true) + "\"}";
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (ps != null) {ps.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				returnResult = "{\"errorMsg\":\"" + e.toString() + "\"}";
			}
		}

		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);
	}

}

class FileUploadLog {
	private String creator;
	private String userName;
	private String fileName;
	private String createdTime;
	private int sn;
	
	
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getCreator() {
		return creator;
	}
	public void setCreator(String creator) {
		this.creator = creator;
	}
	public String getFileName() {
		return fileName;
	}
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
	public String getCreatedTime() {
		return createdTime;
	}
	public void setCreatedTime(String createdTime) {
		this.createdTime = createdTime;
	}
	public int getSn() {
		return sn;
	}
	public void setSn(int sn) {
		this.sn = sn;
	}
}

class ActionLog {
	private String userID;
	private String userName;
	private String actionType;
	private String startTime;

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getUserID() {
		return userID;
	}

	public void setUserID(String userID) {
		this.userID = userID;
	}

	public String getActionType() {
		return actionType;
	}

	public void setActionType(String actionType) {
		this.actionType = actionType;
	}

	public String getStartTime() {
		return startTime;
	}

	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}
}

class CommentLog {
	private String userID;
	private String userName;
	private String comment;
	private String startTime;

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getUserID() {
		return userID;
	}

	public void setUserID(String userID) {
		this.userID = userID;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

	public String getStartTime() {
		return startTime;
	}

	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}
}

class InfoMR {
	private ActionLog[] actionLog;
	private CommentLog[] commentLog;
	private FileUploadLog[] fileUploadLog;
	private ArrayList<TestCase> assignedTC;
	private String submitTC = "尚未執行";
	private String submitTR = "尚未執行";
	private String fillTR = "尚未執行";
	private String reviewTC = "尚未執行";
	private String reviewTR = "尚未執行";
	boolean isTCsubmitted;
	boolean isTCaccepted;
	boolean isTRsubmitted;
	boolean isTRaccepted;

	
	
	public FileUploadLog[] getFileUploadLog() {
		return fileUploadLog;
	}

	public void setFileUploadLog(FileUploadLog[] fileUploadLog) {
		this.fileUploadLog = fileUploadLog;
	}

	public CommentLog[] getCommentLog() {
		return commentLog;
	}

	public void setCommentLog(CommentLog[] commentLog) {
		this.commentLog = commentLog;
	}

	public boolean isTCsubmitted() {
		return isTCsubmitted;
	}

	public void setTCsubmitted(boolean isTCsubmitted) {
		this.isTCsubmitted = isTCsubmitted;
	}

	public boolean isTCaccepted() {
		return isTCaccepted;
	}

	public void setTCaccepted(boolean isTCaccepted) {
		this.isTCaccepted = isTCaccepted;
	}

	public boolean isTRsubmitted() {
		return isTRsubmitted;
	}

	public void setTRsubmitted(boolean isTRsubmitted) {
		this.isTRsubmitted = isTRsubmitted;
	}

	public boolean isTRaccepted() {
		return isTRaccepted;
	}

	public void setTRaccepted(boolean isTRaccepted) {
		this.isTRaccepted = isTRaccepted;
	}

	public ActionLog[] getActionLog() {
		return actionLog;
	}

	public void setActionLog(ActionLog[] actionLog) {
		this.actionLog = actionLog;
	}

	public ArrayList<TestCase> getAssignedTC() {
		return assignedTC;
	}

	public void setAssignedTC(ArrayList<TestCase> assignedTC) {
		this.assignedTC = assignedTC;
	}

	public String getSubmitTC() {
		return submitTC;
	}

	public void setSubmitTC(String submitTC) {
		this.submitTC = submitTC;
	}

	public String getSubmitTR() {
		return submitTR;
	}

	public void setSubmitTR(String submitTR) {
		this.submitTR = submitTR;
	}

	public String getFillTR() {
		return fillTR;
	}

	public void setFillTR(String fillTR) {
		this.fillTR = fillTR;
	}

	public String getReviewTC() {
		return reviewTC;
	}

	public void setReviewTC(String reviewTC) {
		this.reviewTC = reviewTC;
	}

	public String getReviewTR() {
		return reviewTR;
	}

	public void setReviewTR(String reviewTR) {
		this.reviewTR = reviewTR;
	}
}